package db;


import java.sql.*;
import java.text.DecimalFormat;
import java.util.*;
import dao.Student;
public class JdbcUtils {
    // ���ݿ��û���

    private static final String userName = "root";
    // ���ݿ�����
    private static final String password = "123456";
    // ������Ϣ
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static String strSql;
    private static Connection con;
    private static PreparedStatement pStatement;
    private static ResultSet rs;
    private static String url = "jdbc:mysql://localhost:3306/test";

    public static void registerDriver() {
        try {
            Class.forName(DRIVER);// jdbc4.0��ǰ��Ҫ����������ע��
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未注册成功");
        }
    }

    public static void testStudentQuery() throws SQLException {
        strSql = "select * from students limit 10";

        pStatement = con.prepareStatement(strSql);
        rs = pStatement.executeQuery();
        while (rs.next()) {
            Student stu=new Student();
            stu.setId(rs.getInt("id"));
            stu.setName("name");
            stu.setGender("sex");
            stu.setBirthday(rs.getDate("date"));
            System.out.println(stu);
        }
    }

    public static void testBatchInsert1000Records() throws SQLException {
        String sql = "insert into students (name,sex,date) values (?,?,?)";

        PreparedStatement ps = con.prepareStatement(sql);
        final int batchSize = 1000;
        int count = 0;
        long start = System.currentTimeMillis();
        
        System.err.println("Start");
        for (int i = 0; i < 1000; i++) {
            try {
                ps.setString(1, "TEST" + i);
                ps.setString(2, "1");
                ps.setDate(3, new java.sql.Date(System.currentTimeMillis()));
                ps.addBatch();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }
        ps.executeBatch(); // insert remaining records
        ps.close();
        long end = System.currentTimeMillis();
        System.err.println("End" + (end - start));

    }
    
    public static void insert(int n) throws SQLException{
		strSql = "insert into scoreOfStu(name,gender,birthday,javaScore,sportScore,mathScore,sumScore) values(?,?,?,?,?,?,?)";
		pStatement = con.prepareStatement(strSql);
		java.util.Date date=new java.util.Date(System.currentTimeMillis());
		con.setAutoCommit(false);
		for(int i=0;i<n;i++){
			
			double javaS=new Random().nextGaussian(),sportS=new Random().nextGaussian(), mathS=new Random().nextGaussian();
			
			javaS=Math.sqrt(50)*javaS+80;
			sportS=Math.sqrt(50)*sportS+80;
			mathS=Math.sqrt(50)*mathS+80;
			while(javaS<0||javaS>100&&sportS<0||sportS>100&&mathS>100||mathS<0){
				javaS=Math.sqrt(50)*javaS+80;
				sportS=Math.sqrt(50)*sportS+80;
				mathS=Math.sqrt(50)*mathS+80;
			}
			double sumS=javaS+sportS+mathS;
			DecimalFormat df=new DecimalFormat("###.0");
	        pStatement.setString(1, "dai");
	        pStatement.setInt(2, 20);
	        pStatement.setDate(3, new java.sql.Date(date.getTime()));
	        String str=df.format(javaS);
	        pStatement.setString(4, str);
	        str=df.format(sportS);
	        pStatement.setString(5, str);
	        str=df.format(mathS);
	        pStatement.setString(6,str);
	        str=df.format(sumS);
	        pStatement.setString(7,str);
	        pStatement.addBatch();
	        
		}
        pStatement.executeBatch();
        con.commit();
	}

    public static void getStudentByName(Object obj) throws SQLException {
        //������ģ������
        String str = "name";
        if (str.equals(str)) {
            strSql = "select * from students where name like" + "'%" + obj
                    + "'";
        }
        pStatement = con.prepareStatement(strSql);
        rs = pStatement.executeQuery();
        while (rs.next()) {
            Student stu=new Student();
            stu.setId(rs.getInt("id"));
            stu.setName("name");
            stu.setGender("sex");
            stu.setBirthday(rs.getDate("date"));
            
            System.out.println(stu);
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        System.out.println("连接数据库 ...");
        conn = DriverManager.getConnection(url, userName, password);
        System.out.println("连接数据库成功!");
        return conn;
    }

    public static void realeaseAll(ResultSet rs, PreparedStatement st,
            Connection conn) {
        if (rs != null) {
            try {
                rs.close();
                // rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
                // st = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
                // conn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        registerDriver();
        try {
            con = getConnection();
        } catch (SQLException e) {

            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            //testStudentQuery();
//            System.out.println(new java.sql.Date(System.currentTimeMillis()));
//            long t1 = System.currentTimeMillis();
//
//            testBatchInsert1000Records();
//            long t2 = System.currentTimeMillis();
//            System.out.println("添加1000条数据时间 " + (t2 - t1) + "ms");
            long t1 = System.currentTimeMillis();                
            insert(100);
            long t2 = System.currentTimeMillis();
            System.out.println("添加1000条数据时间 " + (t2 - t1) + "ms");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        realeaseAll(rs, pStatement, con);
    }
}
